package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

import co.gov.medellin.ssti.sub.common.util.Utility;
import co.gov.medellin.ssti.sub.model.dao.IBeneficiarioDAO;
import co.gov.medellin.ssti.sub.model.vo.BeneficiarioVO;
import co.gov.medellin.ssti.sub.model.vo.BeneficioVO;

public class BeneficiarioDAOImpl extends BaseDAOImpl implements
		IBeneficiarioDAO {

	private static final String SELECT_GRUPO_FAMILIAR_SISBEN = "SELECT TIPODOC, DOCUMENT, NOM1, NOM2, TO_CHAR(FECHANTO,'DD/MM/YY') FECHANTO, "
			+ " APE1, APE2, SEXO, TELES, DIRECC, HOGAR, "
			+ " ZONA, COMUNA, ESTCIVIL, CARNET, PARENTES,"
			+ " PUNTAJE, FICHA FROM TCS_BEN_SISBEN3_NEW_2012 WHERE (FICHA, HOGAR) IN (SELECT ficha , hogar FROM TCS_BEN_SISBEN3_NEW_2012 WHERE tipodoc = ? AND document = ? ) "
			+ " ORDER BY parentes ";

	private static final String UPDATE_TCS_BEN_BENEFICIARIO = "UPDATE TCS_BEN_BENEFICIARIO SET TIPO_DOCUMENTO = ?, "
			+ " NUMERO_DOCUMENTO = ?, PRIMER_NOMBRE = UPPER(?), "
			+ " SEGUNDO_NOMBRE = UPPER(?), PRIMER_APELLIDO = UPPER(?), "
			+ " SEGUNDO_APELLIDO = UPPER(?), VFL_CODIGO_COMUNA = ?, "
			+ " FECHA_NACIMIENTO = to_date(?,'dd/mm/rrrr'), SEXO = ?, "
			+ " TELEFONO = ?, DIRECCION_DESCRIPTIVA = ?, "
			+ " DIRECCION_ENCASILLADA = ?, VFL_CODIGO_BARRIO = ?, "
			+ " VFL_CODIGO_ZONA = ?, LOCALIZACION_COBAMA = ?, "
			+ " CAMPO_A1 = null , CORREO_ELECTRONICO = ?, "
			+ " ETNIA = ?, USUARIO_MODIFICA = ?, TERMINAL_MODIFICA = ?, "
			+ " FECHA_MODIFICA = TO_DATE(?,'dd/mm/rrrr') "
			+ " WHERE CODIGO = ? ";
	private static final String FIND_GF = "SELECT ben.codigo CODIGOBENE, ben.numero_documento, ben.PRIMER_NOMBRE, "
			+ " ben.SEGUNDO_NOMBRE,ben.PRIMER_APELLIDO, ben.SEGUNDO_APELLIDO, "
			+ " gf.nombre Nombre_Grupo, vflex.DESCRIPCION PARENTESCO, "
			+ " cabeza.CODIGO COD_CABEZA, cabeza.PRIMER_NOMBRE||' '||cabeza.SEGUNDO_NOMBRE||' '||cabeza.PRIMER_APELLIDO"
			+ " ||' '||cabeza.SEGUNDO_APELLIDO NOMBRE_CABEZA, cabeza.NUMERO_DOCUMENTO DOC_CABEZA "
			+ " FROM tcs_ben_beneficiario ben, tcs_ben_grupo_familiar gf, "
			+ " tcs_ben_benef_grupo_familiar bgf, paoyer.tcs_valor_flexible vflex, "
			+ " tcs_ben_beneficiario cabeza "
			+ " WHERE vflex.CODIGO = bgf.VFL_CODIGO_PARENTESCO "
			+ " AND ben.codigo = bgf.ben_codigo AND gf.codigo = bgf.gf_codigo "
			+ " AND ben.CAMPO_A1 IS NULL AND cabeza.CODIGO = gf.BEN_CODIGO "
			+ " AND bgf.gf_codigo = ? ";
	private static final String INSERT_BENEFICIARIO = "INSERT INTO TCS_BEN_BENEFICIARIO"
			+ "(CODIGO, NUMERO_DOCUMENTO, TIPO_DOCUMENTO, PRIMER_NOMBRE,SEGUNDO_NOMBRE, PRIMER_APELLIDO, "
			+ " SEGUNDO_APELLIDO, VFL_CODIGO_COMUNA, FECHA_NACIMIENTO, SEXO, TELEFONO, DIRECCION_DESCRIPTIVA, "
			+ " DIRECCION_ENCASILLADA, LOCALIZACION_COBAMA, CAMPO_A1, VFL_CODIGO_BARRIO, VFL_CODIGO_ZONA, CORREO_ELECTRONICO, ETNIA, "
			+ " usuario_crea,terminal_crea, fecha_crea,usuario_modifica,terminal_modifica,fecha_modifica ) "
			+ " VALUES ( SCS_BEN_BEN.NextVal,?,?,UPPER(?),UPPER(?),UPPER(?),UPPER(?),?,to_date(?,'dd/mm/rrrr'),?,?,?,?,?,null,?,?,?,?,?,?,to_date(?,'dd/mm/rrrr'),?,?,to_date(?,'dd/mm/rrrr'))";

	private static final String FIND_BENEFICIOS_BY_CODBEN = "SELECT * FROM VCS_BENEFICIOS_OTORGADOS WHERE CODBEN = ?";
	private static final String FIND_ULTIMOS_BENEFICIOS = " SELECT bo.*, fcs_ben_getCountValor(bo.CODSUBPROG, bo.CODBEN) ValorBen "
			+ " FROM VCS_BENEFICIOS_OTORGADOS bo "
			+ " WHERE CODBOT in (select max(bot.CODIGO) codigo "
			+ "                FROM tcs_ben_beneficio_otorgado bot "
			+ "                WHERE TO_DATE(bot.FECHA_BENEFICIO,'dd/mm/rrrr') <= TO_DATE(sysdate,'dd/mm/rrrr') "
			+ "                AND bot.BEN_CODIGO = ? "
			+ "                GROUP BY bot.SBPR_CODIGO)";
	private static final String IS_BENEFICIARIO_ASIGNADO = "SELECT CODIGO "
			+ " FROM TCS_BEN_BENEF_GRUPO_FAMILIAR WHERE BEN_CODIGO = ? AND GF_CODIGO = ?";
	private static final String SELECT_ALL_SISBEN = "SELECT * FROM TCS_BEN_SISBEN3_NEW_2012 WHERE 1 = 1 ";
	private static final String SELECT_ALL_VCS_BEN_OTOR_BY_CODBEN_CODSUBPROG = "SELECT * FROM VCS_BENEFICIOS_OTORGADOS WHERE CODBEN = ? AND CODSUBPROG = ?";
	private static final String SELECT_BEN_CODIGO = "SELECT BEN_CODIGO FROM TCS_BEN_BENEFICIO_OTORGADO WHERE codigo = ? ";
	private static final String SELECT_EDAD_BENEFICIARIO_BY_CODIGO = "SELECT TRUNC((SYSDATE-fecha_nacimiento)/365) EDAD FROM TCS_BEN_BENEFICIARIO WHERE CODIGO = ? ";
	private static final String SELECT_MAX_CODIGO = "SELECT MAX(codigo) codigo FROM TCS_BEN_BENEFICIARIO";

	public BeneficiarioDAOImpl(Connection con) {
		super(con);
	}

	public Collection<BeneficiarioVO> findBeneficiarioMEL(
			BeneficiarioVO criteria) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer strQuery = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();
		strQuery.append("SELECT * FROM VCS_BEN_CAMPOS_MEL WHERE 'a'='a' ");
		if (criteria.getTipoDocumento() != null
				&& !criteria.getTipoDocumento().equalsIgnoreCase("")) {
			strQuery.append(" AND TIPODOCUMENTO = ? ");
			parameters.add(criteria.getTipoDocumento());
		}
		if (criteria.getNumeroDocumento() != null
				&& !criteria.getNumeroDocumento().equalsIgnoreCase("")) {
			strQuery.append(" AND NUMERODOCUMENTO = ? ");
			parameters.add(criteria.getNumeroDocumento().trim());
		}
		if (criteria.getNombre1() != null
				&& !criteria.getNombre1().equalsIgnoreCase("")) {
			strQuery.append(" AND NOMBRE1 = UPPER(?) ");
			parameters.add(criteria.getNombre1().trim());
		}
		if (criteria.getNombre2() != null
				&& !criteria.getNombre2().equalsIgnoreCase("")) {
			strQuery.append(" AND NOMBRE2 = UPPER(?) ");
			parameters.add(criteria.getNombre2().trim());
		}
		if (criteria.getPrimerApellido() != null
				&& !criteria.getPrimerApellido().equalsIgnoreCase("")) {
			strQuery.append(" AND APELLIDO1 = UPPER(?) ");
			parameters.add(criteria.getPrimerApellido().trim());
		}
		if (criteria.getSegundoApellido() != null
				&& !criteria.getSegundoApellido().equalsIgnoreCase("")) {
			strQuery.append(" AND APELLIDO2 = UPPER(?) ");
			parameters.add(criteria.getSegundoApellido().trim());
		}
		if (criteria.getFechaNacimiento() != null
				&& !criteria.getFechaNacimiento().equalsIgnoreCase("")) {
			strQuery.append(" AND FECHANACIMIENTO = ? ");
			parameters.add(criteria.getFechaNacimiento().trim());
		}

		ArrayList<BeneficiarioVO> beneficiarios = new ArrayList<BeneficiarioVO>();

		try {
			pstmt = conn.prepareStatement(strQuery.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();
			BeneficiarioVO bene = null;

			while (rst.next()) {
				bene = new BeneficiarioVO();
				bene.setNumeroDocumento(rst.getString("NUMERODOCUMENTO"));
				bene.setTipoDocumento(rst.getString("TIPODOCUMENTO"));
				bene.setNombre1(rst.getString("NOMBRE1"));
				bene.setPrimerApellido(rst.getString("APELLIDO1"));
				bene.setNombre2(rst.getString("NOMBRE2"));
				bene.setSegundoApellido(rst.getString("APELLIDO2"));
				bene.setFechaNacimiento(rst.getString("FECHANACIMIENTO"));
				bene.setSexo(rst.getString("SEXO"));
				bene.setDirDescriptiva(rst.getString("direccion"));
				beneficiarios.add(bene);
				bene = null;
			}
		} finally {
			close(rst, pstmt);
		}
		return beneficiarios;
	}

	public Collection<BeneficiarioVO> findBeneficiarioSisben(
			BeneficiarioVO criteria) throws SQLException {
		if (criteria == null) {
			throw new IllegalArgumentException(
					"El parametro BeneficiarioVO no puede ser null.");
		}
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		Collection<BeneficiarioVO> usuarios = new ArrayList<BeneficiarioVO>();
		StringBuffer stb = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();

		stb.append(SELECT_ALL_SISBEN);
		if (criteria.getNombre1() != null
				&& !"".equalsIgnoreCase(criteria.getNombre1())) {
			stb.append("AND UPPER(NOM1) = UPPER(?) ");
			parameters.add(criteria.getNombre1());
		}
		if (criteria.getNombre2() != null
				&& !"".equalsIgnoreCase(criteria.getNombre2())) {
			stb.append("AND UPPER(NOM2) = UPPER(?) ");
			parameters.add(criteria.getNombre2());
		}
		if (criteria.getPrimerApellido() != null
				&& !"".equalsIgnoreCase(criteria.getPrimerApellido())) {
			stb.append("AND UPPER(APE1) = UPPER(?) ");
			parameters.add(criteria.getPrimerApellido());
		}
		if (criteria.getSegundoApellido() != null
				&& !"".equalsIgnoreCase(criteria.getSegundoApellido())) {
			stb.append("AND UPPER(APE2) = UPPER(?) ");
			parameters.add(criteria.getSegundoApellido());
		}
		if (criteria.getTipoDocumento() != null
				&& !"".equalsIgnoreCase(criteria.getTipoDocumento())) {
			stb.append("AND TIPODOC = ? ");
			parameters.add(criteria.getTipoDocumento());
		}
		if (criteria.getNumeroDocumento() != null
				&& !"".equalsIgnoreCase(criteria.getNumeroDocumento())) {
			stb.append("AND DOCUMENT = ? ");
			parameters.add(criteria.getNumeroDocumento());
		}
		if (criteria.getFechaNacimiento() != null
				&& !"".equalsIgnoreCase(criteria.getFechaNacimiento())) {
			stb.append("AND to_date(FECHANTO,'dd/mm/rrrr') = to_date(?,'dd/mm/rrrr') ");
			parameters.add(criteria.getFechaNacimiento());
		}
		try {
			pstmt = conn.prepareStatement(stb.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				BeneficiarioVO beneficiarioVO = new BeneficiarioVO();

				beneficiarioVO.setNombre1(rst.getString("NOM1"));
				beneficiarioVO.setNombre2(rst.getString("NOM2"));
				beneficiarioVO.setPrimerApellido(rst.getString("APE1"));
				beneficiarioVO.setSegundoApellido(rst.getString("APE2"));
				beneficiarioVO.setTipoDocumento(rst.getString("TIPODOC"));
				beneficiarioVO.setNumeroDocumento(rst.getString("DOCUMENT"));
				beneficiarioVO.setTelefono(rst.getString("TELES"));
				beneficiarioVO.setSexo(rst.getString("SEXO"));
				beneficiarioVO.setFechaNacimientoDate(rst.getDate("FECHANTO"));
				// FORMATEA LA FECHA EN EL ESTILO DD/MM/YYYY
				SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
				String sdate = formatter.format(beneficiarioVO
						.getFechaNacimientoDate());
				beneficiarioVO.setFechaNacimiento(sdate);
				beneficiarioVO.setDirDescriptiva(rst.getString("DIRECC"));
				// beneficiarioVO.setMail(resultSet.getString("DIRECC"));
				Utility.formatSisben(beneficiarioVO);
				usuarios.add(beneficiarioVO);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			close(rst, pstmt);
		}
		return usuarios;
	}

	public Collection<BeneficiarioVO> findGrupoFamiliarSisben(String tipoDoc,
			String numDoc) throws SQLException {
		ResultSet rst = null;
		PreparedStatement pstmt = null;

		List<BeneficiarioVO> familia = new ArrayList<BeneficiarioVO>();

		// Proceso de Validacion
		try {
			pstmt = conn.prepareStatement(SELECT_GRUPO_FAMILIAR_SISBEN);
			// tipodoc = "1" (cedula)
			pstmt.setString(1, "1");
			pstmt.setString(2, numDoc);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				BeneficiarioVO bene = new BeneficiarioVO();
				bene.setNumeroDocumento(rst.getString("DOCUMENT"));
				// test fsr
				bene.setTipoDocumento(Utility
						.deformatSisbenDocumentToSubDocument(rst
								.getString("TIPODOC")));
				bene.setNombre1(rst.getString("NOM1"));
				bene.setPrimerApellido(rst.getString("APE1"));
				bene.setNombre2(rst.getString("NOM2"));
				bene.setSegundoApellido(rst.getString("APE2"));
				bene.setFechaNacimiento(rst.getString("FECHANTO"));
				bene.setSexo(rst.getString("SEXO"));
				bene.setParentesco(rst.getString("PARENTES"));
				bene.setGrupoFamiliar(rst.getString("FICHA"));
				bene.setHogar(rst.getString("HOGAR"));
				// Formatea el parentesco
				Utility.formatSisbenParentesco(bene);
				familia.add(bene);
				bene = null;
			}
		} finally {
			close(rst, pstmt);
		}
		return familia;
	}

	public Collection<BeneficiarioVO> findBeneficiarioByCriteria(
			BeneficiarioVO criteria, String strOrigen) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();

		query.append("SELECT bene.CODIGO CODIGOBENE, bene.NUMERO_DOCUMENTO, "
				+ "bene.TIPO_DOCUMENTO, "
				+ "UPPER(bene.PRIMER_NOMBRE) PRIMER_NOMBRE, UPPER(bene.PRIMER_APELLIDO) PRIMER_APELLIDO, "
				+ "UPPER(bene.SEGUNDO_NOMBRE) SEGUNDO_NOMBRE, UPPER(bene.SEGUNDO_APELLIDO) SEGUNDO_APELLIDO, "
				+ "TO_CHAR(bene.FECHA_NACIMIENTO,'dd/mm/rrrr') FECHA_NACIMIENTO , bene.SEXO, "
				+ "bene.TELEFONO, bene.DIRECCION_DESCRIPTIVA, "
				+ "bene.DIRECCION_ENCASILLADA, bene.LOCALIZACION_COBAMA, "
				+ "bgf.GF_CODIGO GF, gf.NOMBRE NOMBRE_GF, "
				+ "vflexComuna.CODIGO COMUNA, vflexZona.CODIGO COD_ZONA, "
				+ "vflexZona.DESCRIPCION DESC_ZONA, vflexBarrio.CODIGO COD_BARRIO, "
				+ "vflexBarrio.DESCRIPCION DESC_BARRIO, bene.ETNIA, bene.CORREO_ELECTRONICO mail "
				+ "FROM TCS_BEN_BENEFICIARIO bene,paoyer.TCS_VALOR_FLEXIBLE vflexComuna, "
				+ "TCS_BEN_BENEF_GRUPO_FAMILIAR bgf, TCS_BEN_GRUPO_FAMILIAR gf, "
				+ "paoyer.TCS_VALOR_FLEXIBLE vflexZona, paoyer.TCS_VALOR_FLEXIBLE vflexBarrio "
				+ "WHERE vflexComuna.CODIGO (+)= bene.VFL_CODIGO_COMUNA "
				+ "AND vflexZona.CODIGO (+)= bene.VFL_CODIGO_ZONA  "
				+ "AND vflexBarrio.CODIGO (+) = bene.VFL_CODIGO_BARRIO "
				+ "AND bgf.BEN_CODIGO (+)= bene.CODIGO "
				+ "AND gf.CODIGO (+)= bgf.GF_CODIGO ");

		if (!strOrigen.equals("ingresarBeneficiario")) {
			query.append("AND bene.CAMPO_A1 IS NULL ");
		}
		if (criteria.getCode() != null) {
			query.append("AND bene.CODIGO = ? ");
			parameters.add(criteria.getCode());
		}
		if (!(criteria.getNumeroDocumento().trim()).equalsIgnoreCase("")) {
			query.append("AND bene.NUMERO_DOCUMENTO = ? ");
			parameters.add(criteria.getNumeroDocumento().trim());
		}
		if (!criteria.getTipoDocumento().equalsIgnoreCase("")) {
			query.append("AND bene.TIPO_DOCUMENTO = ? ");
			parameters.add(criteria.getTipoDocumento());
		}
		if (!(criteria.getNombre1().trim()).equalsIgnoreCase("")) {
			query.append("AND UPPER(bene.PRIMER_NOMBRE) = UPPER(?) ");
			parameters.add(criteria.getNombre1().trim());
		}
		if (criteria.getNombre2() != null
				&& !(criteria.getNombre2()).trim().equalsIgnoreCase("")) {
			query.append("AND UPPER(bene.SEGUNDO_NOMBRE) = UPPER(?) ");
			parameters.add(criteria.getNombre2().trim());
		}
		if (!criteria.getPrimerApellido().equalsIgnoreCase("")) {
			query.append("AND UPPER(primer_apellido) = UPPER(?) ");
			parameters.add(criteria.getPrimerApellido().trim());
		}
		if (criteria.getSegundoApellido() != null
				&& !(criteria.getSegundoApellido()).trim().equalsIgnoreCase("")) {
			query.append("AND UPPER(segundo_apellido) = UPPER('"
					+ criteria.getSegundoApellido().trim() + "') ");
		}
		if (criteria.getFechaNacimiento() != null) {
			if (!(criteria.getFechaNacimiento().trim()).equalsIgnoreCase("")) {
				query.append("AND to_date(FECHA_NACIMIENTO,'dd/mm/rrrr') = to_date(?,'dd/mm/rrrr') ");
				parameters.add(criteria.getFechaNacimiento());
			}
		}
		query.append(" AND rownum <= 100 ORDER BY bene.NUMERO_DOCUMENTO");

		ArrayList<BeneficiarioVO> beneficiarios = new ArrayList<BeneficiarioVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();
			BeneficiarioVO bene = null;
			while (rst.next()) {
				bene = new BeneficiarioVO();
				bene.setCode(new Long(rst.getLong("CODIGOBENE")));
				bene.setNumeroDocumento(rst.getString("NUMERO_DOCUMENTO"));
				bene.setTipoDocumento(rst.getString("TIPO_DOCUMENTO"));
				bene.setNombre1(rst.getString("PRIMER_NOMBRE"));
				bene.setPrimerApellido(rst.getString("PRIMER_APELLIDO"));
				bene.setNombre2(rst.getString("SEGUNDO_NOMBRE"));
				bene.setSegundoApellido(rst.getString("SEGUNDO_APELLIDO"));
				bene.setVflComuna(rst.getString("COMUNA"));
				bene.setFechaNacimiento(rst.getString("FECHA_NACIMIENTO"));
				bene.setSexo(rst.getString("SEXO"));
				bene.setTelefono(rst.getString("TELEFONO"));
				bene.setDirDescriptiva(rst.getString("DIRECCION_DESCRIPTIVA"));
				bene.setDirEncasillada(rst.getString("DIRECCION_ENCASILLADA"));
				bene.setCodeZona(rst.getString("COD_ZONA"));
				bene.setDescZona(rst.getString("DESC_ZONA"));
				bene.setCodeBarrio(rst.getString("COD_BARRIO"));
				bene.setDescBarrio(rst.getString("DESC_BARRIO"));
				bene.setCobama(rst.getString("LOCALIZACION_COBAMA"));
				bene.setGrupoFamiliarCode(new Long(rst.getLong("GF")));
				bene.setGrupoFamiliar(rst.getString("NOMBRE_GF"));
				bene.setPoseeBeneficios(findPoseeBeneficios(new Long(rst
						.getLong("CODIGOBENE"))));
				bene.setMail(rst.getString("MAIL"));
				bene.setEtnia(rst.getString("ETNIA"));
				beneficiarios.add(bene);
				bene = null;
			}
		} finally {
			close(rst, pstmt);
		}
		return beneficiarios;
	}

	public Collection<BeneficiarioVO> findBeneficiarioByCriteria(
			BeneficiarioVO criteria, String strOrigen, Long codePrograma,
			Long codeSubprograma) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();

		query.append("SELECT DISTINCT(bene.CODIGO) CODIGOBENE, bene.NUMERO_DOCUMENTO, "
				+ "decode(bene.tipo_documento, 'C', 'Cedula de ciudadania', 'E', \n"
				+ "		'Cedula de extranjeria', 'N', 'Nit', 'P', 'Pasaporte', 'R', 'Registro civil', \n"
				+ "		'T', 'Tarjeta de identidad', 'O', 'Otro', 'I', 'NIP', 'U', 'NUIP', 'S', 'NISE',\n"
				+ "		'Cabildo', bene.tipo_documento) TIPO_DOCUMENTO, "
				+ "UPPER(bene.PRIMER_NOMBRE) PRIMER_NOMBRE, UPPER(bene.PRIMER_APELLIDO) PRIMER_APELLIDO, "
				+ "UPPER(bene.SEGUNDO_NOMBRE) SEGUNDO_NOMBRE, UPPER(bene.SEGUNDO_APELLIDO) SEGUNDO_APELLIDO, "
				+ "TO_CHAR(bene.FECHA_NACIMIENTO,'dd/mm/rrrr') FECHA_NACIMIENTO , bene.SEXO, "
				+ "bene.TELEFONO, bene.DIRECCION_DESCRIPTIVA, "
				+ "bene.DIRECCION_ENCASILLADA, "
				+ "bene.LOCALIZACION_COBAMA, "
				+ "bgf.GF_CODIGO GF, gf.NOMBRE NOMBRE_GF, "
				+ "vflexComuna.CODIGO COMUNA, vflexZona.CODIGO COD_ZONA, "
				+ "vflexZona.DESCRIPCION DESC_ZONA, vflexBarrio.CODIGO COD_BARRIO, "
				+ "vflexBarrio.DESCRIPCION DESC_BARRIO, bene.ETNIA, bene.CORREO_ELECTRONICO mail, FCS_BEN_GET_NUMERO_BENEFICIOS(bene.CODIGO) CANTIDADBENEFICIOS "
				+ "FROM TCS_BEN_BENEFICIARIO bene,paoyer.TCS_VALOR_FLEXIBLE vflexComuna, "
				+ "TCS_BEN_BENEF_GRUPO_FAMILIAR bgf, TCS_BEN_GRUPO_FAMILIAR gf, "
				+ "paoyer.TCS_VALOR_FLEXIBLE vflexZona, paoyer.TCS_VALOR_FLEXIBLE vflexBarrio ");

		if (codePrograma != null) {
			query.append(" , TCS_BEN_BENEFICIO_OTORGADO bot, TCS_BEN_SUBPROGRAMA sub, TCS_BEN_PROGRAMA pro "
					+ "WHERE vflexComuna.CODIGO (+)= bene.VFL_CODIGO_COMUNA "
					+ "AND vflexZona.CODIGO (+)= bene.VFL_CODIGO_ZONA  "
					+ "AND vflexBarrio.CODIGO (+) = bene.VFL_CODIGO_BARRIO "
					+ "AND bgf.BEN_CODIGO (+)= bene.CODIGO "
					+ "AND gf.CODIGO (+)= bgf.GF_CODIGO "
					+ "AND bot.SBPR_CODIGO = sub.CODIGO "
					+ "AND pro.CODIGO = sub.PRG_CODIGO "
					+ "AND bene.CODIGO = bot.BEN_CODIGO "
					+ "AND pro.CODIGO = ? ");
			parameters.add(codePrograma);
			Object codsubpro = (Object) codeSubprograma;
			if (codsubpro != null) {
				if (!codeSubprograma.equals("nulo")) {
					query.append("AND sub.CODIGO = ? ");
					parameters.add(codeSubprograma);
				}
			}
		} else {
			query.append("WHERE vflexComuna.CODIGO (+)= bene.VFL_CODIGO_COMUNA "
					+ "AND vflexZona.CODIGO (+)= bene.VFL_CODIGO_ZONA  "
					+ "AND vflexBarrio.CODIGO (+) = bene.VFL_CODIGO_BARRIO "
					+ "AND bgf.BEN_CODIGO (+)= bene.CODIGO "
					+ "AND gf.CODIGO (+)= bgf.GF_CODIGO ");
		}

		if (!strOrigen.equals("ingresarBeneficiario")) {
			query.append("AND bene.CAMPO_A1 IS NULL ");
		}
		if (criteria.getCode() != null) {
			query.append("AND bene.CODIGO = ? ");
			parameters.add(criteria.getCode());
		}
		if (!criteria.getNumeroDocumento().equalsIgnoreCase("")) {
			query.append("AND bene.NUMERO_DOCUMENTO = ? ");
			parameters.add(criteria.getNumeroDocumento().trim());
		}
		if (!criteria.getTipoDocumento().equals("nulo")) {
			query.append("AND bene.TIPO_DOCUMENTO = ? ");
			parameters.add(criteria.getTipoDocumento());
		}
		if (!criteria.getNombre1().equalsIgnoreCase("")) {
			query.append("AND UPPER(bene.PRIMER_NOMBRE) LIKE UPPER(?) ");
			parameters.add("%" + criteria.getNombre1().trim() + "%");
		}
		if (!criteria.getNombre2().equalsIgnoreCase("")) {
			query.append("AND UPPER(bene.SEGUNDO_NOMBRE) LIKE UPPER(?) ");
			parameters.add("%" + criteria.getNombre2().trim() + "%");
		}
		if (!criteria.getPrimerApellido().equalsIgnoreCase("")) {
			query.append("AND UPPER(primer_apellido) like UPPER(?) ");
			parameters.add("%" + criteria.getPrimerApellido().trim() + "%");
		}
		if (!criteria.getSegundoApellido().equalsIgnoreCase("")) {
			query.append("AND UPPER(segundo_apellido) like UPPER(?) ");
			parameters.add("%" + criteria.getSegundoApellido().trim() + "%");
		}
		query.append(" AND rownum <= 5000 ORDER BY bene.NUMERO_DOCUMENTO");

		Collection<BeneficiarioVO> beneficiarios = new LinkedList<BeneficiarioVO>();
		Collection<BeneficiarioVO> beneTemp = new LinkedList<BeneficiarioVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();
			BeneficiarioVO bene = null;
			while (rst.next()) {
				bene = new BeneficiarioVO();
				bene.setCode(new Long(rst.getLong("CODIGOBENE")));
				bene.setNumeroDocumento(rst.getString("NUMERO_DOCUMENTO"));
				bene.setTipoDocumento(rst.getString("TIPO_DOCUMENTO"));
				bene.setNombre1(rst.getString("PRIMER_NOMBRE"));
				bene.setPrimerApellido(rst.getString("PRIMER_APELLIDO"));
				bene.setNombre2(rst.getString("SEGUNDO_NOMBRE"));
				bene.setSegundoApellido(rst.getString("SEGUNDO_APELLIDO"));
				bene.setVflComuna(rst.getString("COMUNA"));
				bene.setFechaNacimiento(rst.getString("FECHA_NACIMIENTO"));
				bene.setSexo(rst.getString("SEXO"));
				bene.setTelefono(rst.getString("TELEFONO"));
				bene.setDirDescriptiva(rst.getString("DIRECCION_DESCRIPTIVA"));
				bene.setDirEncasillada(rst.getString("DIRECCION_ENCASILLADA"));
				bene.setCodeZona(rst.getString("COD_ZONA"));
				bene.setDescZona(rst.getString("DESC_ZONA"));
				bene.setCodeBarrio(rst.getString("COD_BARRIO"));
				bene.setDescBarrio(rst.getString("DESC_BARRIO"));
				bene.setCobama(rst.getString("LOCALIZACION_COBAMA"));
				bene.setGrupoFamiliarCode(new Long(rst.getLong("GF")));
				bene.setGrupoFamiliar(rst.getString("NOMBRE_GF"));
				// bene.setPoseeBeneficios(getNumberBeneficios(new
				// Long(rst.getLong(
				// "CODIGOBENE"))));
				bene.setPoseeBeneficios(rst.getString("CANTIDADBENEFICIOS"));
				bene.setMail(rst.getString("MAIL"));
				bene.setEtnia(rst.getString("ETNIA"));
				beneficiarios.add(bene);
				bene = null;
			}
			if (criteria.getPoseeBeneficios().equalsIgnoreCase("Si")) {
				List<BeneficiarioVO> benefi = (List<BeneficiarioVO>) beneficiarios;
				Long desde = null;
				Long hasta = null;
				Long cantidadBeneficios;

				if (!criteria.getDesdeBOT().equals("")) {
					desde = new Long(criteria.getDesdeBOT());
				}
				if (!criteria.getHastaBOT().equals("")) {
					hasta = new Long(criteria.getHastaBOT());
				}

				for (int i = 0; i < beneficiarios.size(); i++) {
					bene = (BeneficiarioVO) benefi.get(i);
					if (bene.getPoseeBeneficios().equals("0")) {
						beneTemp.add(bene);
					} else if (desde != null && hasta != null) {
						cantidadBeneficios = new Long(bene.getPoseeBeneficios());
						if (cantidadBeneficios.longValue() < desde.longValue()
								|| cantidadBeneficios.longValue() > hasta
										.longValue()) {
							beneTemp.add(bene);
						}
					} else if (desde != null && hasta == null) {
						cantidadBeneficios = new Long(bene.getPoseeBeneficios());
						if (cantidadBeneficios.longValue() < desde.longValue()) {
							beneTemp.add(bene);
						}
					} else if (desde == null && hasta != null) {
						cantidadBeneficios = new Long(bene.getPoseeBeneficios());
						if (cantidadBeneficios.longValue() > hasta.longValue()) {
							beneTemp.add(bene);
						}
					}
				}
				beneficiarios.removeAll(beneTemp);
			} else if (criteria.getPoseeBeneficios().equalsIgnoreCase("No")) {
				List<BeneficiarioVO> benefi = (List<BeneficiarioVO>) beneficiarios;
				for (int i = 0; i < beneficiarios.size(); i++) {
					bene = (BeneficiarioVO) benefi.get(i);
					Long cantidadBeneficios = new Long(
							bene.getPoseeBeneficios());
					if (cantidadBeneficios.longValue() > 0) {
						beneTemp.add(bene);
					}
				}
				beneficiarios.removeAll(beneTemp);
			}
		} finally {
			close(rst, pstmt);
		}
		return beneficiarios;
	}

	public int update(BeneficiarioVO criteria) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<Object> parameters = new ArrayList<Object>();
		List<Object> parameters2 = new ArrayList<Object>();

		// revisar si el numero_documento ya existe.
		String strNumeroDocumento = criteria.getNumeroDocumento().toString();
		if (strNumeroDocumento != null) {
			strNumeroDocumento = strNumeroDocumento.trim();
			String strSQL = "SELECT COUNT(NUMERO_DOCUMENTO) "
					+ "FROM TCS_BEN_BENEFICIARIO "
					+ "WHERE TIPO_DOCUMENTO = ? AND NUMERO_DOCUMENTO = ? AND CODIGO <> ? ";
			parameters2.add(criteria.getTipoDocumento());
			parameters2.add(strNumeroDocumento);
			parameters2.add(criteria.getCode());
			try {
				pstmt = conn.prepareStatement(strSQL);
				pstmt = setQueryParameters(pstmt, parameters2);
				rst = pstmt.executeQuery();
				rst.next();
				if (rst.getInt(1) > 0)
					return -1;
			} finally {
				close(rst, pstmt);
			}
		}

		String strNombre1 = criteria.getNombre1();
		if (criteria.getNombre1() != null) {
			strNombre1 = criteria.getNombre1().trim();
		}
		String strNombre2 = criteria.getNombre2();
		if (criteria.getNombre2() != null) {
			strNombre2 = criteria.getNombre2().trim();
		}
		String primerApellido = criteria.getPrimerApellido();
		if (criteria.getPrimerApellido() != null) {
			primerApellido = criteria.getPrimerApellido().trim();
		}
		String segundoApellido = criteria.getSegundoApellido();
		if (criteria.getSegundoApellido() != null) {
			segundoApellido = criteria.getSegundoApellido().trim();
		}

		parameters.add(criteria.getTipoDocumento());
		parameters.add(strNumeroDocumento);
		parameters.add(strNombre1);
		parameters.add(strNombre2);
		parameters.add(primerApellido);
		parameters.add(segundoApellido);
		parameters.add(criteria.getVflComuna());
		parameters.add(criteria.getFechaNacimiento());
		parameters.add(criteria.getSexo());
		parameters.add(criteria.getTelefono());
		parameters.add(criteria.getDirDescriptiva());
		parameters.add(criteria.getDirEncasillada());
		parameters.add(criteria.getCodeBarrio());
		parameters.add(criteria.getCodeZona());
		parameters.add(criteria.getCobama());
		parameters.add(criteria.getMail());
		parameters.add(criteria.getEtnia());
		parameters.add(criteria.getUpdateUser());
		parameters.add(criteria.getUpdateTerminal());
		parameters.add(criteria.getUpdateDate());

		parameters.add(criteria.getCode());

		try {
			// preparar la sentencia update.
			pstmt = conn.prepareStatement(UPDATE_TCS_BEN_BENEFICIARIO);
			pstmt = setQueryParameters(pstmt, parameters);
			int numRows = pstmt.executeUpdate();
			if (numRows > 0)
				return 1;
			else
				return 0;
		} finally {
			close(rst, pstmt);
		}

	}

	public Collection<BeneficiarioVO> findCabezasGrupoFamiliar(
			BeneficiarioVO criteria) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();

		query.append("SELECT GF.BEN_CODIGO COD_CABEZA, BEN.TIPO_DOCUMENTO, "
				+ "BEN.NUMERO_DOCUMENTO,BEN.PRIMER_NOMBRE, BEN.SEGUNDO_NOMBRE, "
				+ "BEN.PRIMER_APELLIDO, BEN.SEGUNDO_APELLIDO, "
				+ "GF.CODIGO COD_GF, GF.NOMBRE NOM_GF "
				+ "FROM TCS_BEN_GRUPO_FAMILIAR GF, "
				+ "TCS_BEN_BENEFICIARIO BEN "
				+ "WHERE GF.BEN_CODIGO = BEN.CODIGO");

		ArrayList<BeneficiarioVO> cabezas = new ArrayList<BeneficiarioVO>();
		if (!criteria.getNumeroDocumento().equalsIgnoreCase("")) {
			query.append(" AND BEN.NUMERO_DOCUMENTO = ? ");
			parameters.add(criteria.getNumeroDocumento());
		}
		if (!criteria.getNombre1().equalsIgnoreCase("")) {
			query.append(" AND UPPER(BEN.PRIMER_NOMBRE) LIKE UPPER(?) ");
			parameters.add("%" + criteria.getNombre1() + "%");
		}
		if (!criteria.getPrimerApellido().equalsIgnoreCase("")) {
			query.append(" AND UPPER(BEN.PRIMER_APELLIDO||' '||BEN.SEGUNDO_APELLIDO) like UPPER(?) ");
			parameters.add("%" + criteria.getPrimerApellido() + "%");
		}
		if (criteria.getGrupoFamiliar() != null
				&& !criteria.getGrupoFamiliar().equalsIgnoreCase("")) {
			query.append(" AND UPPER(GF.NOMBRE) LIKE UPPER(?) ");
			parameters.add("%" + criteria.getGrupoFamiliar() + "%");
		}
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();
			BeneficiarioVO bene = null;
			while (rst.next()) {
				bene = new BeneficiarioVO();
				bene.setCode(new Long(rst.getLong("COD_CABEZA")));
				bene.setTipoDocumento(rst.getString("TIPO_DOCUMENTO"));
				bene.setNumeroDocumento(rst.getString("NUMERO_DOCUMENTO"));
				bene.setNombre1(rst.getString("PRIMER_NOMBRE"));
				bene.setNombre2(rst.getString("SEGUNDO_NOMBRE"));
				bene.setPrimerApellido(rst.getString("PRIMER_APELLIDO"));
				bene.setSegundoApellido(rst.getString("SEGUNDO_APELLIDO"));
				bene.setGrupoFamiliarCode(new Long(rst.getLong("COD_GF")));
				bene.setGrupoFamiliar(rst.getString("NOM_GF"));
				cabezas.add(bene);
				bene = null;
			}
		} finally {
			close(rst, pstmt);
		}
		return cabezas;
	}

	public Collection<BeneficiarioVO> findGrupoFamiliar(BeneficiarioVO criteria)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ArrayList<BeneficiarioVO> beneficiarios = new ArrayList<BeneficiarioVO>();
		if (criteria.getGrupoFamiliarCode() != null) {
			try {
				pstmt = conn.prepareStatement(FIND_GF);
				pstmt.setLong(1, criteria.getGrupoFamiliarCode().longValue());
				rst = pstmt.executeQuery();
				BeneficiarioVO bene = null;
				while (rst.next()) {
					bene = new BeneficiarioVO();
					bene.setCode(new Long(rst.getLong("CODIGOBENE")));
					bene.setNumeroDocumento(rst.getString("NUMERO_DOCUMENTO"));
					bene.setNombre1(rst.getString("PRIMER_NOMBRE"));
					bene.setNombre2(rst.getString("SEGUNDO_NOMBRE"));
					bene.setPrimerApellido(rst.getString("PRIMER_APELLIDO"));
					bene.setSegundoApellido(rst.getString("SEGUNDO_APELLIDO"));
					bene.setGrupoFamiliar(rst.getString("Nombre_Grupo"));
					bene.setParentesco(rst.getString("PARENTESCO"));
					bene.setPoseeBeneficios(findCountBeneficios(new Long(rst
							.getLong("CODIGOBENE"))));
					bene.setCodeCabezaGF(new Long(rst.getLong("COD_CABEZA")));
					bene.setNombreCabezaGF(rst.getString("NOMBRE_CABEZA"));
					bene.setDocumentoCabezaGF(rst.getString("DOC_CABEZA"));
					beneficiarios.add(bene);
					bene = null;
				}
			} finally {
				close(rst, pstmt);
			}
		}
		return beneficiarios;
	}

	public String findEdad(Long codeBeneficiario) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String strEdad = null;
		try {
			pstmt = conn.prepareStatement(SELECT_EDAD_BENEFICIARIO_BY_CODIGO);
			pstmt.setLong(1, codeBeneficiario.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				strEdad = rst.getString("EDAD");
			}
		} finally {
			close(rst, pstmt);
		}
		return strEdad;
	}

	public String[] insert(BeneficiarioVO criteria) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<Object> parameters = new ArrayList<Object>();
		int numRows = 0;

		// convertir el sexo de convencion sisben a sub. (si es
		// 1->'M', si es 2-> 'F')
		Utility.formatSisbenSex(criteria);
		// criteria.setSexo(criteria.getSexo().equals("1") ? "M" : "F");
		// test fsr. convertir tipo_documento a convencion sub. en sisben hay
		// 0,1,2,3,4
		criteria.setTipoDocumento(Utility
				.deformatSisbenDocumentToSubDocument(criteria
						.getTipoDocumento()));
		try {
			pstmt = conn.prepareStatement(INSERT_BENEFICIARIO);
			pstmt.setString(1, criteria.getNumeroDocumento().trim());
			pstmt.setString(2, criteria.getTipoDocumento());
			pstmt.setString(3, criteria.getNombre1().trim());
			pstmt.setString(4, criteria.getNombre2().trim());
			pstmt.setString(5, criteria.getPrimerApellido().trim());
			pstmt.setString(6, criteria.getSegundoApellido().trim());
			pstmt.setString(7, criteria.getVflComuna());
			pstmt.setString(8, criteria.getFechaNacimiento());
			pstmt.setString(9, criteria.getSexo());
			pstmt.setString(10, criteria.getTelefono());
			pstmt.setString(11, criteria.getDirDescriptiva());
			pstmt.setString(12, criteria.getDirEncasillada());
			pstmt.setString(13, criteria.getCobama());
			pstmt.setString(14, criteria.getCodeBarrio());
			pstmt.setString(15, criteria.getCodeZona());
			pstmt.setString(16, criteria.getMail());
			pstmt.setString(17, criteria.getEtnia());
			pstmt.setString(18, criteria.getCreationUser());
			pstmt.setString(19, criteria.getCreationTerminal());
			pstmt.setString(20, criteria.getCreationDate());
			pstmt.setString(21, criteria.getUpdateUser());
			pstmt.setString(22, criteria.getUpdateTerminal());
			pstmt.setString(23, criteria.getUpdateDate());
			if (criteria.getNombre1() != null) {
				parameters.add(criteria.getNombre1().trim());
			}
			if (criteria.getNombre2() != null) {
				parameters.add(criteria.getNombre2().trim());
			}
			if (criteria.getPrimerApellido() != null) {
				parameters.add(criteria.getPrimerApellido().trim());
			}
			if (criteria.getSegundoApellido() != null) {
				parameters.add(criteria.getSegundoApellido().trim());
			}
			if (criteria.getCodeBarrio() != null) {
				parameters.add(criteria.getCodeBarrio().trim());
			}
			if (criteria.getCodeZona() != null) {
				parameters.add(criteria.getCodeZona().trim());
			}
			numRows = pstmt.executeUpdate();
			if (numRows == 0) {
				throw new SQLException("No se ha ingresado el registro.");
			}
		} finally {
			close(rst, pstmt);
		}
		// obtener el codigo de la fila insertada. Seria mejor un proc.
		// almacenado usando insert - returning para tener mas control.
		// refactorizar esto!.
		try {
			pstmt = conn.prepareStatement(SELECT_MAX_CODIGO);
			rst = pstmt.executeQuery();
			String code = "";
			if (rst.next()) {
				code = rst.getString("codigo");
			}

			String retorno[] = new String[2];
			if (numRows > 0) {
				retorno[0] = "true";
				retorno[1] = code;
				return retorno;
			} else {
				retorno[0] = "false";
				retorno[1] = code;
				return retorno;
			}
		} finally {
			close(rst, pstmt);
		}
	}

	public Collection<BeneficioVO> findBeneficiosByCodBen(Long codeBeneficiario)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		BeneficioVO beneficioVO = null;
		ArrayList<BeneficioVO> beneficios = new ArrayList<BeneficioVO>();
		try {
			pstmt = conn.prepareStatement(FIND_BENEFICIOS_BY_CODBEN);
			pstmt.setLong(1, codeBeneficiario.longValue());
			rst = pstmt.executeQuery();
			while (rst.next()) {
				beneficioVO = new BeneficioVO();
				beneficioVO.setCode(new Long(rst.getLong("CODSUBPROG")));
				beneficioVO.setNombre(rst.getString("NOMSUBPROG"));
				beneficioVO.setFecha(rst.getString("FECHA"));
				beneficioVO.setCodSede(new Long(rst.getLong("CODSEDE")));
				beneficioVO.setNomSede(rst.getString("NOMSEDE"));
				beneficioVO.setCodPrograma(new Long(rst.getLong("CODPROG")));
				beneficioVO.setNomPrograma(rst.getString("NOMPROG"));
				beneficioVO.setCodCategoria(new Long(rst
						.getLong("CODCATEGORIA")));
				beneficioVO.setNomCategoria(rst.getString("NOMCATEGORIA"));
				beneficioVO.setRenovar(rst.getString("RENOVAR"));
				beneficioVO.setDiasReutilizar(rst.getString("DIASREUTILIZAR"));
				beneficioVO.setCodeBot(new Long(rst.getString("CODBOT")));
				beneficios.add(beneficioVO);
				beneficioVO = null;
			}
		} finally {
			close(rst, pstmt);
		}
		return beneficios;
	}

	public Collection<BeneficioVO> findUltimosBeneficios(Long codeBeneficiario)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		BeneficioVO beneficioVO = null;
		ArrayList<BeneficioVO> beneficios = new ArrayList<BeneficioVO>();
		int iTotal = 0;
		int iCount = 0;
		int valor = 0;
		try {
			pstmt = conn.prepareStatement(FIND_ULTIMOS_BENEFICIOS);
			pstmt.setLong(1, codeBeneficiario.longValue());
			rst = pstmt.executeQuery();
			while (rst.next()) {
				beneficioVO = new BeneficioVO();
				beneficioVO.setCode(new Long(rst.getLong("CODSUBPROG")));
				beneficioVO.setNombre(rst.getString("NOMSUBPROG"));
				beneficioVO.setFecha(rst.getString("FECHA"));
				beneficioVO.setCodSede(new Long(rst.getLong("CODSEDE")));
				beneficioVO.setNomSede(rst.getString("NOMSEDE"));
				beneficioVO.setCodPrograma(new Long(rst.getLong("CODPROG")));
				beneficioVO.setNomPrograma(rst.getString("NOMPROG"));
				beneficioVO.setCodCategoria(new Long(rst
						.getLong("CODCATEGORIA")));
				beneficioVO.setNomCategoria(rst.getString("NOMCATEGORIA"));
				beneficioVO.setRenovar(rst.getString("RENOVAR"));
				beneficioVO.setDiasReutilizar(rst.getString("DIASREUTILIZAR"));
				beneficioVO.setCodeBot(new Long(rst.getString("CODBOT")));
				beneficioVO.setCodeArea(new Long(rst.getString("CODAREA")));
				beneficioVO.setNomArea(rst.getString("NOMAREA"));
				beneficioVO.setValor(rst.getInt("ValorBen"));
				valor += rst.getDouble("ValorBen");
				iCount = findCountBeneficios(
						new Long(rst.getLong("CODSUBPROG")),
						new Long((rst.getString("CODBEN"))));
				iTotal += iCount;
				beneficioVO.setNumeroBeneficios(iCount);
				beneficios.add(beneficioVO);
				beneficioVO = null;
			}
			if (beneficios != null && beneficios.size() > 0) {
				beneficioVO = new BeneficioVO();
				beneficioVO.setCode(new Long("0"));
				beneficioVO.setNombre("");
				beneficioVO.setFecha("");
				beneficioVO.setCodSede(new Long("0"));
				beneficioVO.setNomSede("");
				beneficioVO.setCodPrograma(new Long("0"));
				beneficioVO.setNomPrograma("");
				beneficioVO.setCodCategoria(new Long("0"));
				beneficioVO.setNomCategoria("");
				beneficioVO.setRenovar("");
				beneficioVO.setDiasReutilizar("");
				beneficioVO.setCodeBot(new Long("0"));
				beneficioVO.setCodeArea(new Long("0"));
				beneficioVO.setNomArea("TOTAL");
				beneficioVO.setNumeroBeneficios(iTotal);
				beneficioVO.setValor(valor);
				beneficios.add(beneficioVO);
			}
		} finally {
			close(rst, pstmt);
		}
		return beneficios;
	}

	public boolean delete(Long codeBeneficiario, String user, String terminal,
			String fecha) throws SQLException {
		PreparedStatement pstmt = null;

		String query = "UPDATE TCS_BEN_BENEFICIARIO SET CAMPO_A1 = 'S', "
				+ " usuario_modifica = ?, terminal_modifica = ?, "
				+ " fecha_modifica = TO_DATE(?,'dd/mm/rrrr') "
				+ " WHERE CODIGO = ? ";
		int iregAfect = 0;
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, user);
			pstmt.setString(2, "WEB=" + terminal);
			pstmt.setString(3, fecha);
			pstmt.setLong(4, codeBeneficiario.longValue());
			iregAfect = pstmt.executeUpdate();
			if (iregAfect > 0)
				return true;
			else
				return false;
		} finally {
			close(pstmt);
		}
	}

	public boolean deleteBeneficio(Long codeBeneficiario, Long codeBeneficio,
			String user, String terminal, String fecha) throws SQLException {
		PreparedStatement pstmt = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;

		boolean retorno = false;
		if (codeBeneficiario != null && codeBeneficio != null) {
			String query = "SELECT codigo FROM TCS_BEN_BENEFICIO_OTORGADO WHERE SBPR_CODIGO = ? AND BEN_CODIGO = ? ";

			try {
				// conn.setAutoCommit(false);
				pstmt = conn.prepareStatement(query);
				pstmt.setLong(1, codeBeneficio.longValue());
				pstmt.setLong(2, codeBeneficiario.longValue());
				rst = pstmt.executeQuery();
				int iReg = 0;
				while (rst.next()) {
					retorno = true;
					Long codigo = new Long(rst.getLong("codigo"));
					query = "UPDATE TCS_BEN_BENEFICIO_OTORGADO SET campo_a1 = 'E', "
							+ " usuario_modifica = ?, terminal_modifica = ?, "
							+ " fecha_modifica = TO_DATE(?,'dd/mm/rrrr') "
							+ " WHERE codigo = ? ";
					stmt = conn.prepareStatement(query);
					stmt.setString(1, user);
					stmt.setString(2, "WEB=" + terminal);
					stmt.setString(3, fecha);
					stmt.setLong(4, codigo.longValue());
					iReg = stmt.executeUpdate();
					if (iReg == 0) {
						throw new SQLException("No se actualizo el registro.");
					} else if (iReg > 1) {
						throw new SQLException(
								"Se iba a actualizar mas de un registro. Se revierte la operacion.");
					}
				}
				// con.commit();
			} catch (SQLException e) {
				// conn.rollback();
				throw e;
			} finally {
				// con.setAutoCommit(true);
				close(rst, pstmt);
				close(stmt);
			}
		}
		return retorno;
	}

	public boolean isBeneficiarioAsignadoAGrupoFamiliar(
			Long codigoBeneficiario, Long codigoGrupoFamiliar)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		try {
			pstmt = conn.prepareStatement(IS_BENEFICIARIO_ASIGNADO);
			pstmt.setLong(1, codigoBeneficiario.longValue());
			pstmt.setLong(2, codigoGrupoFamiliar.longValue());
			rst = pstmt.executeQuery();
			return rst.next() ? true : false;
		} finally {
			close(rst, pstmt);
		}
	}

	public boolean isBeneficiarioSubprograma(Long codeBeneficiario,
			Long codeSubprograma) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		try {
			pstmt = conn
					.prepareStatement(SELECT_ALL_VCS_BEN_OTOR_BY_CODBEN_CODSUBPROG);
			pstmt.setLong(1, codeBeneficiario.longValue());
			pstmt.setLong(2, codeSubprograma.longValue());
			rst = pstmt.executeQuery();

			return rst.next() ? true : false;
		} finally {
			close(rst, pstmt);
		}
	}

	public Long findCodeBeneficiarioByCodeBOT(Long codeBOT) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		Long codeBen = null;
		try {
			pstmt = conn.prepareStatement(SELECT_BEN_CODIGO);
			pstmt.setLong(1, codeBOT.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				return new Long(rst.getLong("BEN_CODIGO"));
			}
			return codeBen;
		} finally {
			close(rst, pstmt);
		}
	}

	private String findPoseeBeneficios(Long codeBeneficiario)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT CODSUBPROG FROM VCS_BENEFICIOS_OTORGADOS "
				+ "WHERE CODBEN = ? ";
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeBeneficiario.longValue());
			rst = pstmt.executeQuery();
			return rst.next() ? "Si" : "No";
		} finally {
			close(rst, pstmt);
		}
	}

	/**
	 * Metodo para obtener el numero de beneficios Otorgados
	 * 
	 * @param code
	 * @return
	 * @throws java.sql.SQLException
	 */
	private String findCountBeneficios(Long code) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT COUNT(1) NUMBENE FROM (SELECT SBPR_CODIGO, BEN_CODIGO "
				+ " FROM BENEFICIOS.TCS_BEN_BENEFICIO_OTORGADO beneotorg "
				+ " WHERE to_date(beneotorg.FECHA_BENEFICIO,'dd/mm/rrrr') <= to_date(sysdate,'dd/mm/rrrr') "
				+ " AND beneotorg.BEN_CODIGO = ? )";

		int contador = 0;
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, code.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				contador = rst.getInt("NUMBENE");
			}
		} finally {
			close(rst, pstmt);
		}
		return (String.valueOf(contador));
	}

	public int findCountBeneficios(Long codeSubprograma, Long codeBeneficiario)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String strSql = "SELECT COUNT(1) total "
				+ " FROM tcs_ben_beneficio_otorgado bot "
				+ " WHERE TO_DATE(bot.FECHA_BENEFICIO,'dd/mm/rrrr') <= TO_DATE(sysdate,'dd/mm/rrrr') "
				+ " AND bot.BEN_CODIGO = ? AND bot.SBPR_CODIGO = ? ";
		int iTotal = 0;
		try {
			pstmt = conn.prepareStatement(strSql);
			pstmt.setLong(1, codeBeneficiario.longValue());
			pstmt.setLong(2, codeSubprograma.longValue());
			rst = pstmt.executeQuery();
			if (rst.next()) {
				iTotal = rst.getInt("total");
			}
		} finally {
			close(rst, pstmt);
		}
		return iTotal;
	}

}
